﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.OleDb;


    public class ExcelReader
    {
        public ExcelReader() 
        {
        }

        public bool Read(string ExcelPath, out DataSet dsResult)
        {
            bool result = true;
            string returnVal = string.Empty;
            //read Excel
            string ExcelConnString = string.Empty;
            ExcelConnString = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" + ExcelPath + "; Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=1\";";
            //ExcelConnString = "Data Source=" + ExcelPath + "; Format=xls; HDR=Yes; IMEX=1\";";

            OleDbConnection objConn = new OleDbConnection(ExcelConnString);
            DataSet objDataset = null;

            try
            {


                objConn.Open();
                OleDbCommand objCmdSelect = new OleDbCommand("SELECT * FROM [Sheet1$]", objConn);
                OleDbDataAdapter objAdapter = new OleDbDataAdapter(objCmdSelect);
                objDataset = new DataSet();
                objAdapter.Fill(objDataset);
                returnVal = "Data has been successfully imported";
            }

            catch (Exception ex)
            {
               // Tools.LogWriter.LogWriter logwriter = new Tools.LogWriter.LogWriter();
               // logwriter.WriteLog(ex.Message.ToString(), ex.StackTrace.ToString());
                returnVal = "Error while importing data, please contact IT support";
                result = false;
            }

            finally
            {
                if (objConn != null)
                {
                    objConn.Close();
                    objConn.Dispose();
                }
            }

            //----------------------------
            dsResult = objDataset;
            return result;
        }
    }
